<?php
require_once APPLICATION_PATH . "/models/ModelBase.php";

class Modes extends TT_Table_Abstract
{
    protected $_name = 'users';
    protected $_primary = 'uid';
    
    // Tao moi user
    public function new_user($name,$pass)
    {
        $db = $this->getSessionDB();
        $sql = "INSERT INTO users (uid ,name ,pass ,admin ,level ,unauthorized)
        VALUES (NULL , ?, ?, '0', '0', '0')";
        return $db->query($sql,array($name,$pass));
    }
    
    public function checkName($name) //Ham kiem tra trung ten
    {
        $db = $this->getSessionDB();
        $sql = "SELECT uid FROM users WHERE name = ?";
        $a = $db->fetchRow($sql, array($name));
        $b = true;
        if(isset($a->uid)){
            $b = false;
        }
        return $b;
    }
    
    public function removeuser($user)  // Xoa user
    {
        $db=  $this->getSessionDB();
        $sql = "DELETE FROM users WHERE name = ?";
        return $db->query($sql, array($user));
    }
    
    public function moderator()     // Liet ke quan tri vien
    {
        $db = $this->getSessionDB();
        $sql = "SELECT name FROM users WHERE level = '1'";
        $array = array();
        $a = $db->fetchAll($sql);
        return $a;
    }
    
    /*
    public function members()     // Liet ke user
    {
        $db = $this->getSessionDB();
        $sql = "SELECT name FROM users WHERE level = '0' AND admin = '0'";
        $array = array();
        $a = $db->fetchAll($sql);
        return $a;
    }
    */
    public function members($page,$page_rows)     // Liet ke user
    {
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT name FROM users WHERE level = '0' AND admin = '0' LIMIT $page,$page_rows";
        $array = array();
        $a = $db->fetchAll($sql);
        return $a;
    }
    
    public function members_num()     // Liet ke user
    {
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT count(name) as num FROM users WHERE level = '0' AND admin = '0'";
        $array = array();
        $a = $db->fetchAll($sql);
        return $a;
    }
    
    public function addunauthorized($user)   // Cam user dang nhap
    {
        $db = $this->getSessionDB();
        $sql = "UPDATE users SET unauthorized = '1' WHERE name = ?";
        return $db->query($sql,array($user));
    }
    
    public function removeunauthorized($user)   // Bo cam user dang nhap
    {
        $db = $this->getSessionDB();
        $sql = "UPDATE users SET unauthorized = '0' WHERE name = ?";
        return $db->query($sql,array($user));
    }
    
    public function unauthorized_user()     // Liet ke danh sach bi cam
    {
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM users WHERE unauthorized = '1'";
        $array = array();
        $a = $db->fetchAll($sql);
        return $a;
    }
    
    public function new_loan($user_squama,$user_loan,$date_started,$duration,$amount,$description)  // Tao moi giao dich vay
    {
        $h = time();
        $db = $this->getSessionDB();
        $sql = "INSERT INTO loans (loan_id, user_squama, user_loan, date_started, duration, amount, status, description, updated)
                VALUES (NULL , ?, ?, ?, ?, ?, '0', ?, '$h')";
        return $db->query($sql, array($user_squama ,$user_loan ,$date_started ,$duration ,$amount ,$description));
    }
    
    public function setpaid($status,$loan_id)    // Thiet lap giao dich vay da tra
    {
        $a = time();
        $db = $this->getSessionDB();
        $sql = "UPDATE loans SET status =? , updated = '$a' WHERE loan_id =?";
        return $db->query($sql,array($status,$loan_id));
    }
    
    public function checkstatus($name)    // Danh sach user khong du dieu kien vay
    {
        $db = $this->getSessionDB();
        $sql = "SELECT status FROM loans WHERE user_squama=? ORDER BY date_started DESC";    
        return $db->fetchRow($sql,array($name));        
    }
    
    /*
    public function checkispaid()    // Danh sach user khong du dieu kien vay
    {
        $db = $this->getSessionDB();
        $sql = "SELECT DISTINCT user_squama FROM loans WHERE status = '0'";    
        return $db->fetchAll($sql);        
    }
    */
    public function checkispaid($page,$page_rows)    // Danh sach user khong du dieu kien vay
    {
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT user_squama FROM loans WHERE status = '0' ORDER BY loan_id DESC LIMIT $page,$page_rows";    
        return $db->fetchAll($sql);        
    }
    
    public function checkispaid_num()
    {
        $db = $this->getSessionDB();
        $sql = "SELECT count(user_squama) as num FROM loans WHERE status = '0'";    
        return $db->fetchAll($sql);        
    }
    
    
      public function show_overdue($page,$page_rows)   // Qua han tra
    {   $time=mktime(0,0,0);
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM loans WHERE status = '0' and duration < $time ORDER BY loan_id DESC LIMIT $page,$page_rows ";
        return $db->fetchAll($sql) ;          
    }
    public function showoverdue_num()     // Dem so dong
    {
        $time=mktime(0,0,0);
        $db = $this->getSessionDB();
        $sql = "SELECT count(loan_id) as num FROM loans WHERE status = '0' and duration < $time";
        return $db->fetchAll($sql) ;          
    }
     public function show_maturity($page,$page_rows)   // Den han tra
    {   $time=mktime(0,0,0);
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM loans WHERE status = '0' and duration = $time ORDER BY loan_id DESC LIMIT $page,$page_rows ";
        return $db->fetchAll($sql) ;          
    }
    public function showmaturity_num()     // Dem so dong
    {
        $time=mktime(0,0,0);
        $db = $this->getSessionDB();
        $sql = "SELECT count(loan_id) as num FROM loans WHERE status = '0' and duration = $time";
        return $db->fetchAll($sql) ;          
    }
    
    public function checkInfo($username) // Hien thi ten user
    {
        $db = $this->getSessionDB();
        $sql = 'SELECT * FROM users where name = ?';
        return $db->fetchRow($sql, array($username));   
    }
    
    public function checkinfo1($name,$page,$page_rows)   // Phan trang
    {
        $page = (int)$page;
        $page_rows = (int)$page_rows;
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM loans WHERE user_loan=? and  status = '0' ORDER BY loan_id DESC LIMIT $page,$page_rows ";
        return $db->fetchAll($sql,array($name)) ;          
    }
    
     public function checkinfo1_num($name)     // Dem so dong
    {
        $db = $this->getSessionDB();
        $sql = "SELECT count(loan_id) as num FROM loans WHERE user_loan=? and status = '0' ORDER BY loan_id DESC ";
        return $db->fetchAll($sql,array($name)) ;          
    }
    
    public function checkuser_squama($name)    // Danh sach user khong du dieu kien vay
    {              
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM loans WHERE user_squama=?";    
        return $db->fetchAll($sql,array($name));        
    }
        
    public function finduser_squama($name)    // Tim kien ten nguoi vay
    {             
        $db = $this->getSessionDB();        
        $sql = "SELECT * FROM loans WHERE user_squama like ?";    
        return $db->fetchAll($sql,array($name));        
    }         
      
    public function detailuser_squama($name)    // Chi tiet nguoi vay
      {
        $db = $this->getSessionDB();
        $sql = "SELECT * FROM loans WHERE user_squama = ?";    
        return $db->fetchAll($sql,array($name));        
    }       

}
?>